﻿
-- 根据班组人员(含在场人员)、进场出场人员、分包、工种分别统计
-- 增加非法人员统计
CREATE procedure [dbo].[GetTotalcurrentData]
@Isbz bit,
@Isjcc bit,
@Isfb bit,
@Isgz bit,
@Isff bit
as
select * into #tab from Kqjl_info_Copy where sfff=0
-- 班组人员(含在场人员)
if(@isbz=1)
begin
	select isnull(qybmm,'当前总在场人数') qybmm,sum(cnt) cnt
	from
	(
	select isnull(case qybmm when '' then null else qybmm end,'其他') qybmm,count(1) cnt from #tab
	inner join
	(select max(kqsj) sj,sfzh from #tab group by sfzh) aa on(#tab.kqsj=aa.sj and #tab.sfzh=aa.sfzh)
	where jczt=1
	group by qybmm
	) aa
	group by qybmm
	with rollup
end
else
begin
select 1 where 1<>1
end

select sfzh,jczt into #tt from #tab group by sfzh,jczt
-- 进场出场人员
if(@Isjcc=1)
begin	
	select count(1) tj from #tt where jczt=1
	union all
	select count(1) tj from #tt where jczt=0
end
else
begin
select 1 where 1<>1
end

-- 分包人员
if(@Isfb=1)
begin
	select fbm,sum(cnt) cnt
	from
	(
	select fbm,count(1) cnt from #tab
	inner join
	(select max(kqsj) sj,sfzh from #tab group by sfzh) aa on(#tab.kqsj=aa.sj and #tab.sfzh=aa.sfzh)
	where jczt=1
	group by fbm
	) aa
	group by fbm
end
else
begin
select 1 where 1<>1
end

-- 工种人员
if(@Isgz=1)
begin
	select gz,sum(cnt) cnt
	from
	(
	select gz,count(1) cnt from #tab
	inner join
	(select max(kqsj) sj,sfzh from #tab group by sfzh) aa on(#tab.kqsj=aa.sj and #tab.sfzh=aa.sfzh)
	where jczt=1
	group by gz
	) aa
	group by gz
end
else
begin
select 1 where 1<>1
end

-- 非法人员统计
if(@Isff=1)
begin
	select sum(case jczt when 1 then 1 else -1 end) aa
	from Kqjl_info_Copy
	where sfff=1
end
else
begin
select 1 where 1<>1
end